#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Zbfx表Repository - 处理股票推荐数据
负责zbfx表的所有数据访问操作
"""

from typing import List, Dict, Any
from datetime import date
from .base_repository import BaseRepository
from models.stock_model import AggregatedZbfxRecord
from config.config import STOCK_SELECTION


class ZbfxRepository(BaseRepository):
    """Zbfx表数据访问类 - 处理股票推荐数据"""
    
    def __init__(self, connection):
        super().__init__(connection)
        self.table_name = "zbfx"
    

    
    def get_aggregated_stocks(self, target_date: date, aggregation_strategy: str = 'max') -> List[AggregatedZbfxRecord]:
        """
        获取聚合后的股票推荐数据
        
        Args:
            target_date: 目标日期
            aggregation_strategy: 聚合策略 ('max', 'avg', 'weighted', 'sum')
            
        Returns:
            List[AggregatedZbfxRecord]: 聚合后的股票推荐记录列表
        """
        top_count = STOCK_SELECTION['top_count']
        bottom_count = STOCK_SELECTION.get('bottom_count', 0)
        min_strength = STOCK_SELECTION.get('min_strength', 0)
        
        try:
            # 第一步：获取聚合后的股票数据
            base_query = self._build_aggregation_query(aggregation_strategy)
            all_results = self.execute_query(base_query, (target_date, min_strength))
            
            self.logger.info(f"聚合查询完成，找到 {len(all_results)} 只不同的股票")
            
            if not all_results:
                self.logger.warning(f"在日期 {target_date} 没有找到符合条件的股票数据")
                return []
            
            # 第二步：为选中的股票获取合并的理由
            aggregated_records = []
            
            # 获取最高强度的股票
            high_strength_records = all_results[:top_count] if top_count > 0 else []
            for record in high_strength_records:
                reasons = self.get_stock_reasons(record['StockCode'], target_date)
                aggregated_record = self._convert_to_aggregated_record(record, reasons, 'HIGH')
                aggregated_records.append(aggregated_record)
            
            # 获取最低强度的股票（从末尾取）
            if bottom_count > 0 and len(all_results) > top_count:
                remaining_records = all_results[top_count:]
                low_strength_records = remaining_records[-bottom_count:] if len(remaining_records) >= bottom_count else remaining_records
                
                for record in low_strength_records:
                    reasons = self.get_stock_reasons(record['StockCode'], target_date)
                    aggregated_record = self._convert_to_aggregated_record(record, reasons, 'LOW')
                    aggregated_records.append(aggregated_record)
            
            self.logger.info(f"最终选择了 {len([r for r in aggregated_records if r.selection_type == 'HIGH'])} 只高强度股票")
            self.logger.info(f"最终选择了 {len([r for r in aggregated_records if r.selection_type == 'LOW'])} 只低强度股票")
            
            return aggregated_records
            
        except Exception as e:
            self.logger.error(f"聚合查询执行失败: {e}")
            raise
    
    def get_stock_reasons(self, stock_code: str, target_date: date) -> str:
        """
        获取指定股票在指定日期的所有推荐理由（合并）
        
        Args:
            stock_code: 股票代码
            target_date: 目标日期
            
        Returns:
            str: 合并后的推荐理由
        """
        query = """
        SELECT [Reason]
        FROM [GP].[dbo].[zbfx]
        WHERE [StockCode] = ? AND CAST([dtm] AS DATE) = ?
        ORDER BY CAST([Strength] AS FLOAT) DESC
        """
        
        try:
            results = self.execute_query(query, (stock_code, target_date))
            if results:
                reasons = [r['Reason'] for r in results if r['Reason']]
                return ' | '.join(reasons)
            return ''
        except Exception as e:
            self.logger.warning(f"获取股票 {stock_code} 推荐理由失败: {e}")
            return ''
    
    def get_stock_strength_history(self, stock_code: str, days: int = 30) -> List[Dict[str, Any]]:
        """
        获取股票的强度历史记录
        
        Args:
            stock_code: 股票代码
            days: 查询天数
            
        Returns:
            List[Dict]: 强度历史记录
        """
        query = """
        SELECT TOP (?)
            [StockCode], [Reason], [Strength], [dtm]
        FROM [GP].[dbo].[zbfx]
        WHERE [StockCode] = ?
        ORDER BY [dtm] DESC
        """
        
        return self.execute_query(query, (days, stock_code))
    
    def get_top_stocks_by_strength(self, target_date: date, limit: int = 50) -> List[Dict[str, Any]]:
        """
        获取指定日期强度最高的股票
        
        Args:
            target_date: 目标日期
            limit: 返回数量限制
            
        Returns:
            List[Dict]: 按强度排序的股票列表
        """
        query = f"""
        SELECT TOP ({limit})
            [StockCode], 
            MAX(CAST([Strength] AS FLOAT)) as max_strength,
            COUNT(*) as recommendation_count,
            MAX([dtm]) as latest_time
        FROM [GP].[dbo].[zbfx]
        WHERE CAST([dtm] AS DATE) = ?
        GROUP BY [StockCode]
        ORDER BY max_strength DESC
        """
        
        return self.execute_query(query, (target_date,))
    
    def get_recommendation_statistics(self, target_date: date) -> Dict[str, Any]:
        """
        获取指定日期的推荐统计信息
        
        Args:
            target_date: 目标日期
            
        Returns:
            Dict: 统计信息
        """
        query = """
        SELECT 
            COUNT(*) as total_recommendations,
            COUNT(DISTINCT [StockCode]) as unique_stocks,
            AVG(CAST([Strength] AS FLOAT)) as avg_strength,
            MAX(CAST([Strength] AS FLOAT)) as max_strength,
            MIN(CAST([Strength] AS FLOAT)) as min_strength
        FROM [GP].[dbo].[zbfx]
        WHERE CAST([dtm] AS DATE) = ?
        """
        
        result = self.execute_query(query, (target_date,))
        return result[0] if result else {}
    
    def _build_aggregation_query(self, strategy: str) -> str:
        """
        构建聚合查询SQL
        
        Args:
            strategy: 聚合策略
            
        Returns:
            str: SQL查询语句
        """
        if strategy == 'max':
            weighted_field = "MAX(CAST([Strength] AS FLOAT))"
        elif strategy == 'avg':
            weighted_field = "AVG(CAST([Strength] AS FLOAT))"
        elif strategy == 'weighted':
            weighted_field = "(AVG(CAST([Strength] AS FLOAT)) * LOG(COUNT(*) + 1))"
        elif strategy == 'sum':
            weighted_field = "SUM(CAST([Strength] AS FLOAT))"
        else:
            weighted_field = "MAX(CAST([Strength] AS FLOAT))"
        
        return f"""
        SELECT 
            [StockCode],
            MAX(CAST([Strength] AS FLOAT)) as max_strength,
            AVG(CAST([Strength] AS FLOAT)) as avg_strength,
            SUM(CAST([Strength] AS FLOAT)) as sum_strength,
            {weighted_field} as weighted_strength,
            COUNT(*) as record_count,
            MAX([dtm]) as latest_dtm
        FROM [GP].[dbo].[zbfx]
        WHERE CAST([dtm] AS DATE) = ?
        GROUP BY [StockCode]
        HAVING MAX(CAST([Strength] AS FLOAT)) >= ?
        ORDER BY weighted_strength DESC
        """
    
    def _convert_to_aggregated_record(self, db_record: Dict[str, Any], reasons: str, selection_type: str) -> AggregatedZbfxRecord:
        """
        将数据库记录转换为AggregatedZbfxRecord对象
        
        Args:
            db_record: 数据库记录
            reasons: 推荐理由
            selection_type: 选择类型
            
        Returns:
            AggregatedZbfxRecord: 聚合记录对象
        """
        return AggregatedZbfxRecord(
            stock_code=db_record['StockCode'],
            aggregated_reason=reasons,
            max_strength=float(db_record['max_strength']),
            avg_strength=float(db_record['avg_strength']),
            sum_strength=float(db_record['sum_strength']),
            weighted_strength=float(db_record['weighted_strength']),
            record_count=int(db_record['record_count']),
            dtm=db_record['latest_dtm'],
            selection_type=selection_type
        )
